# coding:utf-8
import pymssql
import sys
from mysql_data import session
from log_write import log_class
reload(sys)
sys.setdefaultencoding('utf-8')

log=log_class()

class mssql_session(session):
	def __init__(self):
		try:
			self.conn = pymssql.connect('172.16.20.250','vcenter','palmgo','',timeout=0,login_timeout=0,charset='utf8')
		except Exception as e:
			log.debug('Module:sqlserver.mysql_session:'+str(e))

	def vpx_mem(self):  ##共分配的内存
		##5个 盘阵卷在数据库中的路径 VPX_DATASTORE
		sql = "select SUM(MEM_SIZE_MB) from center.dbo.VPX_VM where \
		file_name like 'ds:///vmfs/volumes/57d7c42e-76b00600-e3b4-1cc1de1b7d50/%' or \
		FILE_NAME like 'ds:///vmfs/volumes/5417ef6b-da14b5d8-246d-3c4a927a8496/%' or \
		file_name like 'ds:///vmfs/volumes/577e55dc-23773e54-7f60-782bcb1296f0/%' or \
		FILE_NAME like 'ds:///vmfs/volumes/577e1d45-a6c92dbc-d613-782bcb1296f0/%' or \
		file_name like 'ds:///vmfs/volumes/577e157b-ca6ed924-73d3-782bcb130c0d/%'"

		info = self.data_output(sql)

		return int(info[0][0])

		'''

		计算 总内存，使用Zabbix ，以下代码注释
		sql="select HOST_ID from VPX_DAS_HB_DATASTORE_SYS GROUP BY HOST_ID"
		info = self.data_output(sql)
		count =0
		for i in  info:
			sql="select MEM_SIZE from VPX_HOST where id ='%s'" %(i[0])
			s=self.data_output(sql)
			count=count+int(s[0][0])
		print count / 1024 /1024 /1024
		'''

	# def vpx_storage(self):
	# 	sql = "SELECT CAPACITY  from center.dbo.VPX_DATASTORE where STORAGE_URL like 'ds:///vmfs/volumes/57d7c42e-76b00600-e3b4-1cc1de1b7d50/%'"
	# 	info = self.data_output(sql)
	#
	# 	sql="select sum(aggr_commited_storage_space) from center.dbo.vpx_vm where file_name like 'ds:///vmfs/volumes/57d7c42e-76b00600-e3b4-1cc1de1b7d50/%'"
	# 	info1=self.data_output(sql)
	#
	# 	return  '%.2f' %(float(info[0][0]) /1024/1024/1024/1024) , '%.2f' %(float(info1[0][0])/1024/1024/1024/1024),int(info[0][0]) - int(info1[0][0])

	def vcenter_total(self):
		sql = "select count(*) from center.dbo.vpx_vm"
		total_vps = self.data_output(sql)   ##共有多少虚拟 机

		sql = "select count(*) from center.dbo.VPX_VM_DS_SPACE WHERE \
		DS_ID= (select VPX_DATASTORE.ID from center.dbo.VPX_DATASTORE where VPX_DATASTORE.STORAGE_URL= 'ds:///vmfs/volumes/57d7c42e-76b00600-e3b4-1cc1de1b7d50/') or \
		DS_ID= (select VPX_DATASTORE.ID from center.dbo.VPX_DATASTORE where VPX_DATASTORE.STORAGE_URL= 'ds:///vmfs/volumes/5417ef6b-da14b5d8-246d-3c4a927a8496/') or \
		DS_ID= (select VPX_DATASTORE.ID from center.dbo.VPX_DATASTORE where VPX_DATASTORE.STORAGE_URL= 'ds:///vmfs/volumes/577e55dc-23773e54-7f60-782bcb1296f0/') or \
		DS_ID= (select VPX_DATASTORE.ID from center.dbo.VPX_DATASTORE where VPX_DATASTORE.STORAGE_URL= 'ds:///vmfs/volumes/577e1d45-a6c92dbc-d613-782bcb1296f0/') or \
		DS_ID= (select VPX_DATASTORE.ID from center.dbo.VPX_DATASTORE where VPX_DATASTORE.STORAGE_URL= 'ds:///vmfs/volumes/577e157b-ca6ed924-73d3-782bcb130c0d/')"

		storage_vps = self.data_output(sql)  ##盘阵上有多少虚拟机
		print storage_vps

		sql = "select count(*) from center.dbo.vpx_host"
		total_host = self.data_output(sql)   ##共有多少主机

		sql = "SELECT HOST_ID FROM center.[dbo].[VPX_HOST_MULTIPATH_LUN] WHERE [SATP_POLICY] LIKE '%ALUA%' GROUP BY  HOST_ID"
		cluster_host=self.data_output(sql)  ##集群内共有主机

		return total_vps[0][0],total_host[0][0],storage_vps[0][0],len(cluster_host)
	def storage_space(self):
		sql = "select CAPACITY,FREE_SPACE from center.dbo.VPX_DATASTORE where \
		STORAGE_URL like 'ds:///vmfs/volumes/57d7c42e-76b00600-e3b4-1cc1de1b7d50/%' or \
		STORAGE_URL like 'ds:///vmfs/volumes/5417ef6b-da14b5d8-246d-3c4a927a8496/%' or \
		STORAGE_URL like 'ds:///vmfs/volumes/577e55dc-23773e54-7f60-782bcb1296f0/%' or \
		STORAGE_URL like 'ds:///vmfs/volumes/577e1d45-a6c92dbc-d613-782bcb1296f0/%' or \
		STORAGE_URL like 'ds:///vmfs/volumes/577e157b-ca6ed924-73d3-782bcb130c0d/%'"
		free_space=0
		total_space=0

		for i in  self.data_output(sql):
			free_space=int(i[1])+free_space
			total_space=int(i[0])+total_space



		sql="select sum(aggr_commited_storage_space) /1024/1024/1024/1024 ,sum(AGGR_UNCOMMITED_STORAGE_SPACE)/1024 /1024/1024/1024 ,\
		sum(AGGR_UNSHARED_STORAGE_SPACE)/1024/1024/1024/1024 \
		from center.dbo.vpx_vm vm where \
		file_name like 'ds:///vmfs/volumes/57d7c42e-76b00600-e3b4-1cc1de1b7d50/%' or \
		file_name like 'ds:///vmfs/volumes/5417ef6b-da14b5d8-246d-3c4a927a8496/%' or \
		file_name like 'ds:///vmfs/volumes/577e157b-ca6ed924-73d3-782bcb130c0d/%' or \
		file_name like 'ds:///vmfs/volumes/577e55dc-23773e54-7f60-782bcb1296f0/%' OR \
		file_name like 'ds:///vmfs/volumes/577e1d45-a6c92dbc-d613-782bcb1296f0/%'"

		info=self.data_output(sql)
		print info

		return '%.2f'%(free_space/1024.0**4),'%.2f'%(total_space/1024.0**4),'%.2f'%(info[0][0]),'%.2f'%(info[0][1]),'%.2f'%(info[0][2])
